package com.vcc.service.impl;

import cn.hutool.core.date.DateField;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson2.JSONObject;
import com.mybatisflex.core.paginate.Page;
import com.mybatisflex.core.query.QueryChain;
import com.mybatisflex.core.query.QueryWrapper;
import com.mybatisflex.core.row.Row;
import com.mybatisflex.spring.service.impl.ServiceImpl;
import com.vcc.core.exception.ProgramException;
import com.vcc.core.model.AmountData;
import com.vcc.core.util.RegexUtil;
import com.vcc.data.entity.*;
import com.vcc.data.mapper.CardOrderMapper;
import com.vcc.data.mapper.ShareAccountDetailMapper;
import com.vcc.service.client.vo.cardOrder.CardOrderVo;
import com.vcc.service.sa.BossUserUtil;
import com.vcc.service.service.*;
import jakarta.annotation.Resource;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import static com.vcc.data.entity.table.BossUserTableDef.BOSS_USER;
import static com.vcc.data.entity.table.CardOrderTableDef.CARD_ORDER;
import static com.vcc.data.entity.table.TransferOrderTableDef.TRANSFER_ORDER;
import static com.vcc.data.entity.table.UserTableDef.USER;
import static com.vcc.data.entity.table.WithdrawTableDef.WITHDRAW;

@Service
public class CardOrderServiceImpl extends ServiceImpl<CardOrderMapper, CardOrder> implements CardOrderService {

    @Resource
    private WalletService walletService;

    @Resource
    private WalletDetailService walletDetailService;

    @Resource
    private CurrencyService currencyService;
    @Autowired
    private ShareAccountDetailMapper shareAccountDetailMapper;
    @Autowired
    private ShareAccountDetailService shareAccountDetailService;

    @Override
    public Page<CardOrder> paging(
            Page<CardOrder> page,
            Long userId,
            Long cardTypeId,
            String orderNo,
            String channel,
            String orderType,
            String orderStatus,
            String cardNumber,
            String startDate,
            String endDate,
            Long userCardId,
            Long dealerId

    ) {
        QueryChain<CardOrder> queriedChain = queryChain();
        queriedChain.leftJoin(USER).on(CARD_ORDER.USER_ID.eq(USER.ID));
        queriedChain.leftJoin(BOSS_USER.as("dealer")).on(USER.BE_LONG_TO_DEALER.eq(BOSS_USER.ID));
        queriedChain.leftJoin(USER.as("opuser")).on(CARD_ORDER.OPERATOR_USER_ID.eq(USER.as("opuser").ID).and(CARD_ORDER.ORDER_SOURCE.eq("CLIENT")));
        queriedChain.leftJoin(BOSS_USER.as("opboss")).on(CARD_ORDER.OPERATOR_USER_ID.eq(BOSS_USER.as("opboss").ID).and(CARD_ORDER.ORDER_SOURCE.eq("SYSTEM")));
        queriedChain.select(CARD_ORDER.ALL_COLUMNS);
        queriedChain.select(USER.ACCOUNT.as("account"));
        queriedChain.select(
                "coalesce(opuser.email,opboss.email) as operatorAccount"
        );
        queriedChain.select(BOSS_USER.EMAIL.as("dealerAccount"));
        Long bossUserId = BossUserUtil.getIdNotNull();
        if (!BossUserUtil.hasAllData()) {
            queriedChain.and(
                    BOSS_USER.as("dealer").ID.eq(bossUserId)
                            .or(BOSS_USER.as("dealer").PARENT_ID.eq(bossUserId)));
        } else {
            if (dealerId != null) {
                queriedChain.and(
                        BOSS_USER.as("dealer").ID.eq(dealerId)
                                .or(BOSS_USER.as("dealer").PARENT_ID.eq(dealerId)));
            }
        }
        if (userId != null) {
            queriedChain.eq(CardOrder::getUserId, userId);
        }
        if (cardTypeId != null) {
            queriedChain.eq(CardOrder::getCardTypeId, cardTypeId);
        }
        if (StringUtils.isNotBlank(orderNo)) {
            queriedChain.eq(CardOrder::getOrderNo, orderNo);
        }
        if (StringUtils.isNotBlank(channel)) {
            queriedChain.eq(CardOrder::getChannel, channel);
        }
        if (StringUtils.isNotBlank(orderType)) {
            queriedChain.eq(CardOrder::getOrderType, orderType);
        }
        if (StringUtils.isNotBlank(orderStatus)) {
            queriedChain.eq(CardOrder::getOrderStatus, orderStatus);
        }
        if (StringUtils.isNotBlank(cardNumber)) {
            queriedChain.eq(CardOrder::getCardNumber, cardNumber);
        }
        if (userCardId != null) {
            queriedChain.eq(CardOrder::getCardNumberId, userCardId);
        }
        if (startDate != null) {
            queriedChain.ge(CardOrder::getRequestTime, DateUtil.beginOfDay(DateUtil.parse(startDate, "yyyy-MM-dd")));
        }
        if (endDate != null) {
            queriedChain.le(CardOrder::getRequestTime, DateUtil.endOfDay(DateUtil.parse(endDate, "yyyy-MM-dd")));
        }

        queriedChain.orderBy(CardOrder::getRequestTime, false);
        return page(
                page,
                queriedChain
        );

    }


    @Override
    public Page<CardOrderVo> paging(Page<CardOrderVo> pageable, Long userId, Long targetUserId, Long cardTypeId, String orderNo, String channel, Role role, String orderType, String orderStatus, String cardNumber, Date startDate, Date endDate, String orderSource, Long userCardId) {
        QueryChain<CardOrder> queryChain = queryChain();
        queryChain.select(CARD_ORDER.ALL_COLUMNS);
        queryChain.select(USER.ACCOUNT.as("account"));
        queryChain.leftJoin(USER).on(CARD_ORDER.USER_ID.eq(USER.ID));
        if (role.getHasAllData()) {
            queryChain.where(USER.PARENT_ID.eq(userId).or(USER.ID.eq(userId)));
            if (RegexUtil.checkObjectIsNotNull(targetUserId)) {
                queryChain.eq(CardOrder::getUserId, targetUserId);
            }
        } else {
            queryChain.where(CARD_ORDER.USER_ID.eq(userId));
        }
        if (RegexUtil.checkObjectIsNotNull(cardTypeId)) {
            queryChain.eq(CardOrder::getCardTypeId, cardTypeId);
        }
        if (RegexUtil.checkStringIsNotNull(orderNo)) {
            queryChain.eq(CardOrder::getOrderNo, orderNo);
        }
        if (RegexUtil.checkStringIsNotNull(channel)) {
            queryChain.eq(CardOrder::getChannel, channel);
        }
        if (RegexUtil.checkStringIsNotNull(orderType)) {
            queryChain.eq(CardOrder::getOrderType, orderType);
        }
        if (RegexUtil.checkStringIsNotNull(orderStatus)) {
            queryChain.eq(CardOrder::getOrderStatus, orderStatus);
        }
        if (RegexUtil.checkStringIsNotNull(cardNumber)) {
            queryChain.eq(CardOrder::getCardNumber, cardNumber);
        }
        if (RegexUtil.checkObjectIsNotNull(startDate)) {
            queryChain.ge(CardOrder::getRequestTime, startDate);
        }
        if (userCardId != null) {
            queryChain.eq(CardOrder::getCardNumberId, userCardId);
        }
        if (RegexUtil.checkObjectIsNotNull(endDate)) {
            queryChain.le(CardOrder::getRequestTime, endDate);
        }
        queryChain.orderBy(CardOrder::getCreateDate, false);
        return this.pageAs(pageable, queryChain, CardOrderVo.class);
    }

    @Override
    public CardOrder getByOrderId(String orderId) {
        return queryChain()
                .eq(CardOrder::getOrderNo, orderId)
                .oneOpt()
                .orElseThrow(() -> new ProgramException("Order not found"));
    }

    @Override
    public Boolean updateStatusByOrderId(String orderId, Integer status) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .update();
    }

    @Override
    public Boolean updateStatusAndCardNumberByOrderId(String orderId, Integer status, Long cardNumberId, String cardNumber) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .set(CardOrder::getCardNumberId, cardNumberId)
                .set(CardOrder::getCardNumber, cardNumber)
                .update();
    }

    @Override
    @Transactional
    public Boolean openCardComplete(String orderId, String cardNumber, Long cardNumberId, String status) {
        CardOrder cardOrder = getByOrderId(orderId);
        Currency currency = currencyService.getByCode(cardOrder.getOrderCurrency());
        if (status.equals("FAIL")) {
            // 失败
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "FAIL")
                    .update();
            // 失败后需要退回钱包
            BigDecimal balance = walletService.addUsableQuotaByUserIdAndCurrencyId(
                    cardOrder.getUserId(),
                    currency.getId(),
                    cardOrder.getOrderAmount(),
                    1,
                    true
            );
            walletDetailService.accountRecharge(
                    "R" + cardOrder.getOrderNo(),
                    "原开卡订单" + cardOrder.getOrderNo() + "失败，退回钱包",
                    cardOrder.getOrderAmount(),
                    balance,
                    currency.getId(),
                    cardOrder.getUserId()
            );
            return true;
        } else if (status.equals("SUCCESS")) {
            // 成功
            walletDetailService.setCardNumberAndCardNUmberId(
                    orderId,
                    cardNumber,
                    cardNumberId
            );
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "SUCCESS")
                    .set(CardOrder::getCardNumber, cardNumber)
                    .set(CardOrder::getCardNumberId, cardNumberId)
                    .set(CardOrder::getCompleteTime, new Date())
                    .update();
            return true;
        }
        return null;
    }


    @Override
    public Boolean openShareCardComplete(String orderId, String cardNumber, Long cardNumberId, String status) {
        if (status.equals("FAIL")) {
            // 失败
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "FAIL")
                    .update();
            return true;
        } else {
            // 成功
            shareAccountDetailService.setCardNumberIdByOrderId(
                    orderId,
                    cardNumberId
            );
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "SUCCESS")
                    .set(CardOrder::getCardNumber, cardNumber)
                    .set(CardOrder::getCardNumberId, cardNumberId)
                    .set(CardOrder::getCompleteTime, new Date())
                    .update();

            return true;
        }
    }

    @Override
    public Boolean rechargeCardComplete(String orderId, String cardNumber, Long cardNumberId, String status) {
        CardOrder cardOrder = getByOrderId(orderId);
        Currency currency = currencyService.getByCode(cardOrder.getOrderCurrency());
        if (status.equals("FAIL")) {
            // 失败
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "FAIL")
                    .update();
            // 失败后需要退回钱包
            for (WalletDetail walletDetail : walletDetailService.listByOrderId(orderId)) {
                BigDecimal balance = walletService.addUsableQuotaByUserIdAndCurrencyId(
                        cardOrder.getUserId(),
                        currency.getId(),
                        walletDetail.getAmount(),
                        1,
                        true
                );
                walletDetailService.accountRecharge(
                        "R" + cardOrder.getOrderNo(),
                        "原充值订单" + cardOrder.getOrderNo() + "失败，退回钱包",
                        walletDetail.getAmount(),
                        balance,
                        currency.getId(),
                        cardOrder.getUserId()
                );
            }

            return true;
        } else if (status.equals("SUCCESS")) {
            // 成功
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "SUCCESS")
                    .set(CardOrder::getCardNumber, cardNumber)
                    .set(CardOrder::getCardNumberId, cardNumberId)
                    .set(CardOrder::getCompleteTime, new Date())
                    .update();
            return true;
        }
        return null;
    }

    @Override
    public Boolean withdrawCardComplete(String orderId, String cardNumber, Long cardNumberId, String status) {
        CardOrder cardOrder = getByOrderId(orderId);
        if (status.equals("FAIL")) {
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getOrderStatus, "FAIL")
                    .set(CardOrder::getCompleteTime, new Date())
                    .update();
            return true;
        } else {
            // 成功
            updateChain()
                    .eq(CardOrder::getOrderNo, orderId)
                    .set(CardOrder::getCompleteTime, new Date())
                    .set(CardOrder::getOrderStatus, "SUCCESS")
                    .update();
            // 失败后需要退回钱包
            walletService.cardWithdraw(
                    cardOrder.getUserId(),
                    cardOrder.getOperatorUserId(),
                    cardOrder.getOrderCurrency(),
                    orderId,
                    cardOrder.getCardTypeId(),
                    cardNumber,
                    cardNumberId,
                    cardOrder.getChannel(),
                    cardOrder.getOrderAmount(),
                    cardOrder.getOrderRemark(),
                    cardOrder.getOrderSource()
            );
            return true;
        }
    }

    @Override
    public Boolean setLimitComplete(String orderId, String status) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .set(CardOrder::getCompleteTime, new Date())
                .update();
    }

    @Override
    public Boolean blockCardComplete(String orderId, String status) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .set(CardOrder::getCompleteTime, new Date())
                .update();

    }

    @Override
    public Boolean unblockCardComplete(String orderId, String status) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .set(CardOrder::getCompleteTime, new Date())
                .update();
    }

    @Override
    public Boolean closeCardComplete(String orderId, String status) {
        return updateChain()
                .eq(CardOrder::getOrderNo, orderId)
                .set(CardOrder::getOrderStatus, status)
                .set(CardOrder::getCompleteTime, new Date())
                .update();
    }

    @Override
    public CardOrder openCard(Long shareAccountId,String orderId, Long cardTypeId, Long userId, Long operatorUserId, String channel, BigDecimal orderAmount, String orderCurrency, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .orderStatus("PENDING")
                .orderAmount(orderAmount)
                .orderCurrency(orderCurrency)
                .orderType("OPEN")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .shareAccountId(shareAccountId)
                .build();
        save(order);
        return order;
    }


    @Override
    public CardOrder rechargeCard(String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, BigDecimal orderAmount, String orderCurrency, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderAmount(orderAmount)
                .orderCurrency(orderCurrency)
                .orderType("RECHARGE")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .build();
        save(order);
        return order;
    }

    @Override
    public CardOrder withdrawCard(String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, BigDecimal orderAmount, String orderCurrency, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderAmount(orderAmount)
                .orderCurrency(orderCurrency)
                .orderType("WITHDRAW")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .build();
        save(order);
        return order;
    }

    @Override
    public CardOrder setLimit(Long shareAccountId, String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, BigDecimal limitAmount, String limitCurrency, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderAmount(limitAmount)
                .orderCurrency(limitCurrency)
                .orderType("SET_LIMIT")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .shareAccountId(shareAccountId)
                .build();
        save(order);
        return order;
    }

    @Override
    public CardOrder blockCard(String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderType("BLOCK")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .build();
        save(order);
        return order;
    }

    @Override
    public CardOrder unblockCard(String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderType("UNBLOCK")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .build();
        save(order);
        return order;
    }

    @Override
    public CardOrder closeCard(String orderId, Long cardTypeId, Long userId, Long operatorUserId, String cardNumber, Long cardNumberId, String channel, String remark, String orderSource) {
        CardOrder order = CardOrder.builder()
                .userId(userId)
                .operatorUserId(operatorUserId)
                .cardTypeId(cardTypeId)
                .channel(channel)
                .orderNo(orderId)
                .cardNumber(cardNumber)
                .cardNumberId(cardNumberId)
                .orderStatus("PENDING")
                .orderType("CLOSE")
                .requestTime(new Date())
                .orderRemark(remark)
                .orderSource(orderSource)
                .build();
        save(order);
        return order;
    }

    @Override
    public List<AmountData> countAmountByUserId(Long userId, String orderType, Date startDate, Date endDate) {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "DATE_FORMAT(create_date, '%Y-%m-%d') as date",
                        "SUM(order_amount) as amount"
                )
                .from(CardOrder.class)
                .and(CardOrder::getCreateDate).between(
                        cn.hutool.core.date.DateUtil.beginOfDay(startDate),
                        cn.hutool.core.date.DateUtil.endOfDay(endDate)
                )
                .and(CardOrder::getOrderType).eq(orderType)
                .groupBy("DATE_FORMAT(create_date, '%Y-%m-%d')")
                .orderBy("date", true); // 按日期降序
        query.eq(CardOrder::getUserId, userId);
        return mapper.selectListByQueryAs(query, AmountData.class);
    }

    @Override
    public List<JSONObject> statistics() {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "user_id as userId",
                        //计算开卡数和销卡数
                        "COUNT(CASE WHEN order_type = 'OPEN' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS openCount",
                        "COUNT(CASE WHEN order_type = 'CLOSE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS closeCount",
                        //计算orderType为Recharge且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS rechargeAmount",
                        "COUNT(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS rechargeCount",
                        //计算orderType为Withdraw且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS withdrawAmount",
                        "COUNT(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS withdrawCount"
                )
                .from(CardOrder.class)
                .and(CardOrder::getCreateDate).between(
                        com.vcc.core.util.DateUtil.getDayBeginDate(DateTime.now().offset(DateField.DAY_OF_MONTH, -1)),
                        com.vcc.core.util.DateUtil.getDayEndDate(DateTime.now().offset(DateField.DAY_OF_MONTH, -1)))
                .groupBy(CARD_ORDER.USER_ID);
        return mapper.selectListByQueryAs(query, Row.class).stream().map(JSONObject::new).toList();
    }

    @Override
    public List<JSONObject> statistics(DateTime startDate, DateTime endDate) {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "user_id as userId",
                        //计算开卡数和销卡数
                        "COUNT(CASE WHEN order_type = 'OPEN' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS openCount",
                        "COUNT(CASE WHEN order_type = 'CLOSE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS closeCount",
                        //计算orderType为Recharge且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS rechargeAmount",
                        "COUNT(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS rechargeCount",
                        //计算orderType为Withdraw且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS withdrawAmount",
                        "COUNT(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS withdrawCount"
                )
                .from(CardOrder.class)
                .and(CardOrder::getCreateDate).between(startDate, endDate)
                .groupBy(CARD_ORDER.USER_ID);
        return mapper.selectListByQueryAs(query, Row.class).stream().map(JSONObject::new).toList();
    }

    @Override
    public List<JSONObject> statistics(DateTime startDate, DateTime endDate, Long userId, Long dealerId) {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "DATE_FORMAT(vcc_card_order.create_date, '%Y-%m-%d') as date",
                        //计算开卡数和销卡数
                        "COUNT(CASE WHEN vcc_card_order.order_type = 'OPEN' AND vcc_card_order.order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS openCount",
                        "COUNT(CASE WHEN vcc_card_order.order_type = 'CLOSE' AND vcc_card_order.order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS closeCount",
                        //计算orderType为Recharge且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN vcc_card_order.order_type = 'RECHARGE' AND vcc_card_order.order_status = 'SUCCESS' THEN vcc_card_order.order_amount ELSE 0 END) AS rechargeAmount",
                        "COUNT(CASE WHEN vcc_card_order.order_type = 'RECHARGE' AND vcc_card_order.order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS rechargeCount",
                        //计算orderType为Withdraw且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN vcc_card_order.order_type = 'WITHDRAW' AND vcc_card_order.order_status = 'SUCCESS' THEN vcc_card_order.order_amount ELSE 0 END) AS withdrawAmount",
                        "COUNT(CASE WHEN vcc_card_order.order_type = 'WITHDRAW' AND vcc_card_order.order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS withdrawCount"
                )
                .from(CardOrder.class)
                .and(CardOrder::getCreateDate).between(startDate, endDate)
                .groupBy("DATE_FORMAT(vcc_card_order.create_date, '%Y-%m-%d')");
        if (userId != null) {
            query.eq("vcc_card_order.user_id", userId);
        }
        if (dealerId != null) {
            query.leftJoin(USER).on(CARD_ORDER.USER_ID.eq(USER.ID));
            query.leftJoin(BOSS_USER).on(USER.BE_LONG_TO_DEALER.eq(BOSS_USER.ID));
            query.and(BOSS_USER.ID.eq(dealerId).or(BOSS_USER.PARENT_ID.eq(dealerId)));
        }
        return mapper.selectListByQueryAs(query, Row.class).stream().map(JSONObject::new).toList();
    }

    @Override
    public List<JSONObject> cardTypeStatistics(DateTime startDate, DateTime endDate, List<Long> cardTypeIds) {
        QueryWrapper query = QueryWrapper.create()
                .select(
                        "card_type_id as cardTypeId",
                        //计算开卡数和销卡数
                        "COUNT(CASE WHEN order_type = 'OPEN' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS openCount",
                        "COUNT(CASE WHEN order_type = 'CLOSE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS closeCount",
                        //计算orderType为Recharge且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS rechargeAmount",
                        "COUNT(CASE WHEN order_type = 'RECHARGE' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS rechargeCount",
                        //计算orderType为Withdraw且状态是SUCCESS的总金额 和总条数
                        "SUM(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN order_amount ELSE 0 END) AS withdrawAmount",
                        "COUNT(CASE WHEN order_type = 'WITHDRAW' AND order_status = 'SUCCESS' THEN 1 ELSE NULL END) AS withdrawCount"
                )
                .from(CardOrder.class)
                .and(CardOrder::getCreateDate).between(DateUtil.beginOfDay(startDate), DateUtil.endOfDay(endDate))
                .groupBy(CARD_ORDER.CARD_TYPE_ID);
        if (cardTypeIds != null && cardTypeIds.size() > 0) {
            query.where(CARD_ORDER.CARD_TYPE_ID.in(cardTypeIds));
        }
        return mapper.selectListByQueryAs(query, Row.class).stream().map(JSONObject::new).toList();
    }
}
